﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Dotnet.Sui;
using System.Linq.Dynamic.Core;




namespace Sui.WinForm
{
    partial class FrmZfb
    {
        private DataTable GetGroupBySum(DataTable dt)
        {
            DataTable dtGroupBy = dt.DefaultView.ToTable(false, "收支", "交易状态", "资金状态");
            DataTable dtSum = dtGroupBy.DefaultView.ToTable();
            if (dtSum.Columns.Contains("汇总") == false)
            {
                dtSum.Columns.Add("汇总", typeof(System.String));
            }
            if (dtSum.Columns.Contains("条数") == false)
            {
                dtSum.Columns.Add("条数", typeof(System.Int32));
            }
            if (dt.Rows.Count == 0)
                return dtSum;
            dtSum = dt.AsEnumerable().GroupBy(r => new { 收支 = r["收支"], 交易状态 = r["交易状态"], 资金状态 = r["资金状态"] }).
                Select(
                g =>
                {
                    var row = dtSum.NewRow();
                    row["收支"] = g.Key.收支;
                    row["交易状态"] = g.Key.交易状态;
                    row["资金状态"] = g.Key.资金状态;
                    row["汇总"] = string.Format("{0:N2}", g.Sum(r => (decimal)r["金额"]));
                    row["条数"] = g.Count();
                    return row;
                }).CopyToDataTable();
            return dtSum;
        }
        private DataTable GetDynamicGroupBySum(DataTable dt,params string[] columns)
        {
            GetDataTableYMD(dt);
            StringBuilder sb = new StringBuilder();
            foreach (string str in columns)
            {
                sb.Append(str).Append(",");
            }
            DataTable dtSum = dt.DefaultView.ToTable(true, columns);
            if (columns.Length == 0) dtSum.Columns.Clear();
            if (dtSum.Columns.Contains("汇总") == false)
            {
                dtSum.Columns.Add("汇总", typeof(System.String));
            }
            if (dtSum.Columns.Contains("条数") == false)
            {
                dtSum.Columns.Add("条数", typeof(System.Int32));
            }
            dtSum.Rows.Clear();

            var groupByStr = $" new ({sb.ToString().TrimEnd(',')}) ";
            var selector = "new ( Sum(Convert.ToDecimal(it.金额)) as 汇总,Count() as 条数,it.Key)";
            var query =  dt.AsEnumerable().AsQueryable().GroupBy(groupByStr).Select(selector);
            var groupList = query.ToDynamicList();
            foreach (var item in groupList)
            {
                DataRow dr = dtSum.NewRow();
                foreach (string key in columns)
                {
                    dr[key] = item.Key[key];
                }
                dr["汇总"] = string.Format("{0:N2}", item.汇总);
                dr["条数"] = item.条数;
                dtSum.Rows.Add(dr);
            }
            return dtSum;
        }
        private void GetDataTableYMD(DataTable dt)
        {
            if (dt.Columns.Contains("年")==false) dt.Columns.Add("年", typeof(System.Int32));
            if (dt.Columns.Contains("月") == false) dt.Columns.Add("月", typeof(System.Int32));
            if (dt.Columns.Contains("日") == false) dt.Columns.Add("日", typeof(System.Int32));
            if (dt.Columns.Contains("时") == false) dt.Columns.Add("时", typeof(System.Int32));
            if (dt.Columns.Contains("分") == false) dt.Columns.Add("分", typeof(System.Int32));

            foreach(DataRow dr in dt.Rows)
            {
                DateTime crTime = DateTime.Parse(dr["创建时间"].ToString());
                dr["年"] = crTime.Year;
                dr["月"] = crTime.Month;
                dr["日"] = crTime.Day;
                dr["时"] = crTime.Hour;
                dr["分"] = crTime.Minute;
            }
        }

        private void ClearControlText()
        {
            cmbSz.Text = "";
            cmbJyzt.Text = "";
            cmbZjzt.Text = "";
            cmbBz.Text = "";
            cmbFufei.Text = "";
            txtJeEnd.Text = "";
            txtJeStart.Text = "";
            cmbJydf.Text = "";
            cmbSpmc.Text = "";
            cmbLyd.Text = "";
            txtShow.Clear();
        }

        private void SetDgvColumnWidth()
        {
            //ucDgvMain.DataGridView.Columns["id"].Width = 100;
            //ucDgvMain.DataGridView.Columns["id"].AutoSizeMode = DataGridViewAutoSizeColumnMode.None;
            //ucDgvMain.DataGridView.Columns["交易对方"].Width = 150;
            //ucDgvMain.DataGridView.Columns["交易对方"].AutoSizeMode = DataGridViewAutoSizeColumnMode.None;
            //ucDgvMain.DataGridView.Columns["商品名称"].Width = 150;
            //ucDgvMain.DataGridView.Columns["商品名称"].AutoSizeMode = DataGridViewAutoSizeColumnMode.None;
            //ucDgvMain.DataGridView.Columns["订单号"].Width = 150;
            //ucDgvMain.DataGridView.Columns["订单号"].AutoSizeMode = DataGridViewAutoSizeColumnMode.None;
        }

        private string GetUserSelectSql()
        {
            string sql = $"select *from Zfb where 创建时间<='{dtpEnd.Value.ToString("yyyy-MM-dd")} 23:59:59' and 创建时间>='{dtpStart.Value.ToString("yyyy-MM-dd")} 00:00:00'";// ORDER by 创建时间 desc ";
            if (string.IsNullOrEmpty(cmbSz.Text) == false)
            {
                string where = SqlWhere("收支", cmbSz.Text.Trim());
                sql += where;
            }
            if (string.IsNullOrEmpty(cmbJyzt.Text) == false)
            {
                string where = SqlWhere("交易状态", cmbJyzt.Text.Trim());
                sql += where;
            }
            if (string.IsNullOrEmpty(cmbZjzt.Text) == false)
            {
                string where = SqlWhere("资金状态", cmbZjzt.Text.Trim());
                sql += where;
            }
            if (string.IsNullOrEmpty(txtJeStart.Text) == false && string.IsNullOrEmpty(txtJeEnd.Text) == false)
            {
                double jeStart = Double.Parse(txtJeStart.Text);
                double jeEnd = Double.Parse(txtJeEnd.Text);
                string temp = $" and 金额 >='{jeStart}' and 金额<='{jeEnd}'";
                sql += temp;
            }
            if (string.IsNullOrEmpty(cmbJydf.Text) == false)
            {
                string where = SqlWhere("交易对方", cmbJydf.Text.Trim());
                sql += where;
            }
            if (string.IsNullOrEmpty(cmbSpmc.Text) == false)
            {
                string where = SqlWhere("商品名称", cmbSpmc.Text.Trim());
                sql += where;
            }
            if (string.IsNullOrEmpty(cmbLx.Text) == false)
            {
                string where = SqlWhere("类型", cmbLx.Text.Trim());
                sql += where;
            }
            if (string.IsNullOrEmpty(cmbLyd.Text) == false)
            {
                string where = SqlWhere("来源地", cmbLyd.Text.Trim());
                sql += where;
            }
            if (string.IsNullOrEmpty(cmbFufei.Text) == false)
            {
                if (cmbFufei.Text == "大于0")
                {
                    string temp = $" and  服务费>0";
                    sql += temp;
                }
                else
                {
                    string temp = $" and  服务费=0";
                    sql += temp;
                }
            }
            if (string.IsNullOrEmpty(cmbBz.Text) == false)
            {
                if (cmbBz.Text == "空值")
                {
                    string temp = $" and  备注==''";
                    sql += temp;
                }
                else
                {
                    string temp = $" and  备注<>''";
                    sql += temp;
                }
            }
            sql += " ORDER by 创建时间 desc";
            return sql;
        }
        private string SqlWhere(string colName, string whereVal)
        {
            string sql = "";
            if (whereVal.StartsWith("!"))
            {
                string temp = $" and {colName} not like '%{whereVal.TrimStart('!')}%'";
                sql += temp;
            }
            else if (whereVal.Contains(","))
            {
                StringBuilder sb = new StringBuilder("(");
                string[] wheres=whereVal.Split(',');
                foreach(string item in whereVal.Split(','))
                {
                    sb.Append($"'{item}',");
                }
                string xx=sb.ToString().TrimEnd(',')+")";
                string temp = $" and {colName} in {xx}";
                sql += temp;
            }
            else
            {
                string temp = $" and {colName} like '%{whereVal}%'";
                sql += temp;
            }
            return sql;
        }

        //账号分类
        private string GetCmbZh()
        {
            if (cmbZh3.SelectedItem != null)
            {
                var account = cmbZh3.SelectedItem as Account;
                if (account.ChildAccount != null)
                {
                    MessageBox.Show("请选择子级账号");
                    return "";
                }
                if (account == null) return "";
                return account.Id;
            }

            if (cmbZh2.SelectedItem != null)
            {
                var account = cmbZh2.SelectedItem as Account;
                if (account.ChildAccount != null)
                {
                    MessageBox.Show("请选择子级账号");
                    return "";
                }
                if (account == null) return "";
                return account.Id;
            }
            if (cmbZh1.SelectedItem != null)
            {
                var account = cmbZh1.SelectedItem as Account;
                if (account.ChildAccount != null)
                {
                    MessageBox.Show("请选择子级账号");
                    return "";
                }
                if (account == null) return "";
                return account.Id;
            }
            return "";
        }
        //收入分类
        private string GetCmbSr()
        {
            if (cmbSr2.SelectedItem != null)
            {
                var account = cmbSr2.SelectedItem as Account;
                if (account.ChildAccount != null)
                {
                    MessageBox.Show("请选择子级收入分类");
                    return "";
                }
                if (account == null) return "";
                return account.Id;
            }

            if (cmbSr1.SelectedItem != null)
            {
                var account = cmbSr1.SelectedItem as Account;
                if (account.ChildAccount != null)
                {
                    MessageBox.Show("请选择子级收入分类");
                    return "";
                }
                if (account == null) return "";
                return account.Id;
            }
            return "";
        }

        //支出分类
        private string GetCmbZc()
        {
            if (cmbZc2.SelectedItem != null)
            {
                var account = cmbZc2.SelectedItem as Account;
                if (account.ChildAccount != null)
                {
                    MessageBox.Show("请选择子级支出分类");
                    return "";
                }
                if (account == null) return "";
                return account.Id;
            }

            if (cmbZc1.SelectedItem != null)
            {
                var account = cmbZc1.SelectedItem as Account;
                if (account.ChildAccount != null)
                {
                    MessageBox.Show("请选择子级支出分类");
                    return "";
                }
                if (account == null) return "";
                return account.Id;
            }
            return "";
        }
    }
}
